package drMadLap.android.talkyourday;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TalkYourDayDBManager extends SQLiteOpenHelper {
	
	private static final String DEBUG_DB = "{TaklYourDayDBManager}";
	
	private SQLiteDatabase db;
	
	public TalkYourDayDBManager(Context context)
	{
		super(context, "TalkYourDayDB.db", null, 1);
		
		InitDB();
	}
	
	@Override
	public void onCreate(SQLiteDatabase arg_db) {
		// TODO Auto-generated method stub
		try
		{
			arg_db.execSQL("CREATE TABLE issue ( _id INTEGER PRIMARY KEY AUTOINCREMENT," 
					+ " IssueSubject TEXT,"
					+ " mustwrite BOOL);");
			arg_db.execSQL("CREATE TABLE record (_id INTEGER PRIMARY KEY AUTOINCREMENT," 
					+ " datetime TEXT,"
					+ " Weather TEXT,"
					+ " Issue1 TEXT,"
					+ " Content1 TEXT,"
					+ " Issue2 TEXT,"
					+ " Content2 TEXT,"
					+ " Issue3 TEXT,"
					+ " Content3 TEXT);");
			arg_db.execSQL("INSERT INTO issue(IssueSubJect, mustwrite) VALUES ('What''s do day', 'FALSE');");
			arg_db.execSQL("INSERT INTO issue(IssueSubJect, mustwrite) VALUES ('What''s your job', 'FALSE');");
			arg_db.execSQL("INSERT INTO issue(IssueSubJect, mustwrite) VALUES ('How your family', 'FALSE');");
			
		}
		catch(Exception ex)
		{
			Log.d(DEBUG_DB, "onCreate : " + ex.toString());
		}
		

	}
	
	private void InitDB()
	{
		if( this.GetIssueListCount() < 3 )
		{
			this.DeleteIssueData("");
			
			this.AddIssue("What''s do day", false);
			this.AddIssue("What''s your job", false);
			this.AddIssue("How your family", false);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}
	
	public List<IssueData> GetIssueList()
	{
		List<IssueData> IssueList = new ArrayList<IssueData>();
		//SQLiteDatabase db;
		db = this.getReadableDatabase();
		Cursor csr = null;
		
		try
		{
			csr = db.query("issue", new String[] {"IssueSubject", "mustwrite"}, null, null, null, null, null);
			while(csr.moveToNext())
			{
				IssueData newItem = new IssueData();
				newItem.Issue = csr.getString(0);
				newItem.RequiredUse = csr.getString(1).equals("TRUE");
				IssueList.add(newItem);
			}
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
		finally
		{
			if(csr != null)
				csr.close();
			this.close();
		}
		
		return IssueList;
	}
	
	public void CommitIssueList(List<IssueData> IssueList)
	{
		db = this.getWritableDatabase();
		
		ContentValues rows = new ContentValues();
		for(IssueData data : IssueList)
		{
			rows.put("IssueSubject", data.Issue);
			rows.put("mustwrite", data.RequiredUse);
		}
		
		try
		{
			DeleteIssueData("");
			db.insert("issue", null, rows);
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
		
		this.close();
	}
	
	public void DeleteIssueData(String TargetIssue)
	{
		db = this.getWritableDatabase();
		
		try
		{
			if(TargetIssue.length() == 0)
			{
				db.delete("issue", null, null);
			}
			else
			{
				String deleteQuery = "DELETE FROM issue where IssueSubject = '" 
						+ TargetIssue + "'";
				db.execSQL(deleteQuery);
			}
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
		
		this.close();
	}

	public int GetIssueListCount() 
	{
		// TODO Auto-generated method stub
		int nReturn = 0;
		nReturn = this.GetIssueList().size();
		return nReturn;
	}

	public void AddIssue(String newIssue, boolean IsRequired) {
		// TODO Auto-generated method stub
		db = this.getWritableDatabase();
		
		ContentValues rows = new ContentValues();
		rows.put("IssueSubject", newIssue);
		rows.put("mustwrite", IsRequired);
		
		try
		{
			db.insert("issue", null, rows);
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
		
		this.close();
	}
	
	public void AddContent(
			String Issue0, String Answer0,
			String Issue1, String Answer1,
			String Issue2, String Answer2)
	{
		db = this.getWritableDatabase();
		
		ContentValues rows = new ContentValues();
		

		long time = System.currentTimeMillis();
		Date today = new Date(time);       
		String todayStr = SimpleDateFormat.getDateInstance(SimpleDateFormat.LONG,
				Locale.getDefault()).format(today).toUpperCase();
		
		rows.put("datetime", todayStr);
		rows.put("Weather", "");
		rows.put("Issue1", Issue0);
		rows.put("Content1", Answer0);
		rows.put("Issue2", Issue1);
		rows.put("Content2", Answer1);
		rows.put("Issue3", Issue2);
		rows.put("Content3", Answer2);
		
		try
		{
			db.insert("record", null, rows);
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
	}
	
	public List<ContentData> GetContentList()
	{
		List<ContentData> result_list = new ArrayList<ContentData>();
		
		db = this.getReadableDatabase();
		Cursor csr = null;
		
		try
		{
			csr = db.query("record", 
					new String[] {"datetime", "Weather", "Issue1", "Content1", "Issue2", "Content2", "Issue3", "Content3"}, 
					null, null, null, null, null);
			while(csr.moveToNext())
			{
				ContentData newItem = new ContentData(
						csr.getString(0),
						csr.getString(1),
						csr.getString(2),
						csr.getString(3),
						csr.getString(4),
						csr.getString(5),
						csr.getString(6),
						csr.getString(7));
				
				result_list.add(newItem);
			}
		}
		catch(Exception ex)
		{
			Log.e(DEBUG_DB, ex.toString());
		}
		finally
		{
			if(csr != null)
				csr.close();
			this.close();
		}
		
		return result_list;
	}

}
